package com.hxci.pointmap.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.List;

public class DBUtils {

    private String user = "root";
    String dbPwd = "669988";
//    private String url = "jdbc:mysql://101.200.214.138:3309/pointdb";
    private String url = "jdbc:mysql://localhost:3306/pointmap" +
        "?characterEncoding=utf8&useUnicode=true";
    private String driver = "com.mysql.cj.jdbc.Driver";
    public List <Map<String,Object>> doQuery(String sql, List parmList){
        List <Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url,user,dbPwd);
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
//给处理sql语句的参数赋值
            for (int i = 0; i < parmList.size(); i++){
                preparedStatement.setObject(i+1,parmList.get(i));
            }
            ResultSet resultSet =preparedStatement.executeQuery();
//resultSet转List<Map>

            while (resultSet.next()){
                Map<String,Object> rowMap = new HashMap<String,Object>();
                ResultSetMetaData metadata = resultSet.getMetaData();
                int columnCount = metadata.getColumnCount();
                for (int i = 1; i <= columnCount; i++){
                    String columnName = metadata.getColumnName(i);
                    Object value = resultSet.getObject(columnName);
                    rowMap.put(columnName,value);
                }
                resultList.add(rowMap);
            }
            preparedStatement.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return resultList;
    }

    public static void main(String[] args) {
        String sql = "SELECT * FROM sys_user WHERE login_account = ? AND login_pwd = ?";
        List parmList = new ArrayList();
        parmList.add("admin");
        parmList.add("nosubmit");
        DBUtils dbUtils = new DBUtils();
        List <Map<String,Object>> resultList = new DBUtils().doQuery(sql,parmList);
        System.out.println("resultList"+resultList);
        System.out.println("Hello World!");



    }

    public int doUpdate(String sql, List parmList) {
        try{
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url,user,dbPwd);
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            for (int i = 0; i < parmList.size(); i++){
                preparedStatement.setObject(i+1,parmList.get(i));
            }
            int i = preparedStatement.executeUpdate();
            preparedStatement.close();
            conn.close();
            return i;
        }catch(Exception e){
            e.printStackTrace();
        }

        return 0;
    }
}